/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package db.JDBC;

import core.Book;
import core.Course;
import core.Program;
import core.School;
import core.User;
import db.interfaces.BaseDAO;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

/**
 *
 * @author Anton
 */
public abstract class BaseJdbcDAO implements BaseDAO
{
    private Connection con;

    /**
     * 
     * @param con
     */
    public BaseJdbcDAO(Connection con)
    {
        this.con = con;
    }

    /**
     * 
     * @param course
     */
    protected void removeCoursefromUsers(Course course)
    {
        if(course == null)
            return;
        String sql = "DELETE FROM UserCourse WHERE course = ' " + course.getId() + "'";
        delete(sql);        
    }
    /**
     * 
     * @param program
     */
    protected void removeProgramfromUsers(Program program)
    {
        if(program == null)
            return;
        String sql = "DELETE FROM UserProgram WHERE program = ' " + program.getId() + "'";
        delete(sql);
    }

    /**
     * 
     * @param id
     * @return
     */
    protected  User getUserByUserId(long id)
    {
        String sql = "SELECT * FROM Users WHERE id='" + id + "';";
        return createUser(sqlExecuteQuery(sql));
    }
    /**
     * 
     * @param id
     * @return
     */
    protected Book getBookById(long id)
    {
        String sql = "SELECT * FROM Books WHERE id='" + id + "';";
        return createBook(sqlExecuteQuery(sql));
    }
    /**
     * 
     * @param id
     * @return
     */
    protected Course getCourseById(long id)
    {
        String sql = "SELECT * FROM Courses WHERE id='" + id + "';";
        return createCourse(sqlExecuteQuery(sql));
    }
    /**
     * 
     * @param id
     * @return
     */
    protected Program getProgramById(long id)
    {
        String sql = "SELECT * FROM Programs WHERE id='" + id + "';";
        return createProgram(sqlExecuteQuery(sql));
    }
    /**
     * 
     * @param id
     * @return
     */
    protected School getSchoolById(long id)
    {
        String sql = "SELECT * FROM Schools WHERE id='" + id + "';";
        return createSchool(sqlExecuteQuery(sql));
    }


    
    /**
     * 
     * @param sqlCommand
     * @return
     */
    protected boolean contains(String sqlCommand)
    {
        if(sqlCommand == null)
            return false;
        try{
            return sqlExecuteQuery(sqlCommand).next();
        }catch(SQLException e){
            e.printStackTrace();
        }
        return false;        
    }
    /**
     * 
     * @param sqlCommand
     * @return
     */
    protected boolean add(String sqlCommand)
    {
        if(sqlCommand == null)
            return false;
        return addEditDelete(sqlCommand);
    }
    /**
     * 
     * @param sqlCommand
     * @return
     */
    protected boolean edit(String sqlCommand)
    {
        if(sqlCommand == null)
            return false;
        return addEditDelete(sqlCommand);
    }
    /**
     * 
     * @param sqlCommand
     * @return
     */
    protected boolean delete(String sqlCommand)
    {
        if(sqlCommand == null)
            return false;
        return addEditDelete(sqlCommand);
    }
    /**
     * 
     * @param sqlCommand
     * @return
     */
    private boolean addEditDelete(String sqlCommand)
    {
        if(sqlCommand == null)
            return false;
        if(sqlUpdate(sqlCommand) > 0)
            return true;
        else
            return false;
    }


    /**
     * 
     * @param rs
     * @param idField
     * @return
     */
    private Long createId(ResultSet rs, String idField)
    {
        if(rs == null || idField == null)
            return null;
        try{
            if(rs.next())
            {
                return new Long(rs.getLong(idField));
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        return null;
    }
    /**
     * 
     * @param rs
     * @param idField
     * @return
     */
    protected Collection<Long> createIdCollection(ResultSet rs, String idField)
    {
        if(rs == null || idField == null)
            return null;
        Collection<Long> ids = new ArrayList<Long>();

        Long id;
        while((id = createId(rs, idField)) != null)
            ids.add(id);
        return ids;
    }

    /**
     * 
     * @param rs
     * @return
     */
    protected Book createBook(ResultSet rs)
    {
        if(rs == null)
            return null;
        try{
            if(rs.next())
            {
                return new Book(
                        rs.getLong("id"),
                        rs.getString("isbn10"),
                        rs.getString("isbn13"),
                        rs.getString("title"),
                        rs.getString("author"),
                        rs.getString("description"),
                        rs.getString("weblink"),
                        rs.getInt("price")
                        );
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        return null;
    }
    /**
     * 
     * @param rs
     * @return
     */
    protected Collection<Book> createBookCollection(ResultSet rs)
    {
        if(rs == null)
            return null;
        Collection<Book> books = new ArrayList<Book>();

        Book book;
        while((book = createBook(rs)) != null)
            books.add(book);
        return books;
    }

    /**
     * 
     * @param ids
     * @return
     */
    protected Collection<Book> createBookCollection(Collection<Long> ids)
    {
        if(ids == null)
            return null;
        Collection<Book> books = new ArrayList<Book>();

        for(Long id : ids)
        {
            Book book = getBookById(id);
            if(book != null)
                books.add(book);
        }
        return books;
    }

    /**
     * 
     * @param rs
     * @return
     */
    protected User createUser(ResultSet rs)
    {
        if(rs == null)
            return null;
        try{
            if(rs.next())
            {
                return new User(
                        rs.getLong("id"),
                        rs.getString("fname"),
                        rs.getString("lname"),
                        rs.getString("email"),
                        rs.getString("password"),
                        rs.getInt("level"));
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        return null;
    }
    /**
     * 
     * @param rs
     * @return
     */
    protected Collection<User> createUserCollection(ResultSet rs)
    {
        if(rs == null)
            return null;
        Collection<User> users = new ArrayList<User>();

        User user;
        while((user = createUser(rs)) != null)
            users.add(user);
        return users;
    }
    /**
     * 
     * @param ids
     * @return
     */
    protected Collection<User> createUserCollection(Collection<Long> ids)
    {
        if(ids == null)
            return null;
        Collection<User> users = new ArrayList<User>();

        for(Long id : ids)
        {
            User user = getUserByUserId(id);
            if(user != null)
                users.add(user);
        }
        return users;
    }

    /**
     * 
     * @param rs
     * @return
     */
    protected Course createCourse(ResultSet rs)
    {
        if(rs == null)
            return null;
        try{
            if(rs.next())
            {
                Course course = new Course(
                        rs.getLong("id"),
                        rs.getString("name"),
                        rs.getString("description"),
                        rs.getString("weblink"),
                        rs.getString("courseid"));
                String sql = "SELECT * FROM CourseBook WHERE course='" + course.getId() + "';";
                course.setBooks(createBookCollection(createIdCollection(sqlExecuteQuery(sql), "book")));
                return course;
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        return null;
    }
    /**
     * 
     * @param rs
     * @return
     */
    protected  Collection<Course> createCourseCollection(ResultSet rs)
    {
        if(rs == null)
            return null;
        Collection<Course> courses = new ArrayList<Course>();

        Course course;
        while((course = createCourse(rs)) != null)
            courses.add(course);
        return courses;
    }
    /**
     * 
     * @param ids
     * @return
     */
    protected Collection<Course> createCourseCollection(Collection<Long> ids)
    {
        if(ids == null)
            return null;
        Collection<Course> courses = new ArrayList<Course>();

        for(Long id : ids)
        {
            Course course = getCourseById(id);
            if(course != null)
                courses.add(course);
        }
        return courses;
    }

    /**
     * 
     * @param rs
     * @return
     */
    protected Program createProgram(ResultSet rs)
    {
        if(rs == null)
            return null;
        try{
            if(rs.next())
            {
                return new Program(
                        rs.getLong("id"),
                        rs.getString("name"),
                        rs.getString("description"),
                        rs.getString("weblink"),
                        rs.getString("programid"));
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        return null;
    }
    /**
     * 
     * @param rs
     * @return
     */
    protected Collection<Program> createProgramCollection(ResultSet rs)
    {
        if(rs == null)
            return null;
        Collection<Program> programs = new ArrayList<Program>();

        Program program;
        while((program = createProgram(rs)) != null)
            programs.add(program);
        return programs;
    }
    /**
     * 
     * @param ids
     * @return
     */
    protected Collection<Program> createProgramCollection(Collection<Long> ids)
    {
        if(ids == null)
            return null;
        Collection<Program> programs = new ArrayList<Program>();

        for(Long id : ids)
        {
            Program program = getProgramById(id);
            if(program != null)
                programs.add(program);
        }
        return programs;
    }

    /**
     * 
     * @param rs
     * @return
     */
    protected School createSchool(ResultSet rs)
    {
        if(rs == null)
            return null;
        try{
            if(rs.next())
            {
                return new School(
                        rs.getLong("id"),
                        rs.getString("name"),
                        rs.getString("description"),
                        rs.getString("weblink"),
                        rs.getString("XMLSource"));
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        return null;
    }
    /**
     * 
     * @param rs
     * @return
     */
    protected Collection<School> createSchoolCollection(ResultSet rs)
    {
        if(rs == null)
            return null;
        Collection<School> schools = new ArrayList<School>();

        School school;
        while((school = createSchool(rs)) != null)
            schools.add(school);
        return schools;
    }
    /**
     * 
     * @param ids
     * @return
     */
    protected Collection<School> createSchoolCollection(Collection<Long> ids)
    {
        if(ids == null)
            return null;
        Collection<School> schools = new ArrayList<School>();

        for(Long id : ids)
        {
            School school = getSchoolById(id);
            if(school != null)
                schools.add(school);
        }
        return schools;
    }

    /**
     * 
     * @param book
     * @return
     */
    protected boolean bookIsBound(Book book)
    {
        if(book == null)
            return false;
        String sql = "SELECT * FROM CourseBook WHERE book='" + book.getId() + "';";
        return contains(sql);
    }

    /**
     * 
     * @param course
     * @return
     */
    protected boolean courseIsBound(Course course)
    {
        if(course == null)
            return false;
        String sql = "SELECT * FROM ProgramCourse WHERE course='" + course.getId() + "';";
        String sql2 = "SELECT * FROM SchoolCourse WHERE course='" + course.getId() + "';";
        return contains(sql) && contains(sql2);
    }

    /**
     * 
     * @param program
     * @return
     */
    protected boolean programIsBound(Program program)
    {
        if(program == null)
            return false;
        String sql = "SELECT * FROM SchoolProgram WHERE program='" + program.getId() + "';";
        return contains(sql);
    }



    /**
     * 
     * @param table
     * @return
     */
    protected long getLatestAddedId(String table)
    {
        if(table == null)
            return -1;
        String sql = "SELECT MAX(id) AS 'maxId' FROM " + table +";";
        ResultSet rs = sqlExecuteQuery(sql);
        try{
            rs.next();
            return rs.getLong("maxId");
        }catch(SQLException e){
            e.printStackTrace();
        }
        return -1;
    }

    /**
     * 
     * @param sqlCommand
     * @return
     */
    protected int sqlUpdate(String sqlCommand)
    {
        if(sqlCommand == null)
            return 0;
        try
        {
            Statement statement = con.createStatement();
            return statement.executeUpdate(sqlCommand);
        } catch (SQLException e){
                e.printStackTrace();
        }
        return 0;
    }

    /**
     * 
     * @param sqlCommand
     * @return
     */
    protected ResultSet sqlExecuteQuery(String sqlCommand)
    {
        if(sqlCommand == null)
            return null;
        try
        {
            return con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY).executeQuery(con.nativeSQL(sqlCommand));
        }catch(SQLException ex){
            ex.printStackTrace();
        }
        return null;
    }

}
